[Looker] Period over Period 分析をLookMLで実装するその壱 #looker

ルッポップ (L-PoP) #1

「カスタマーストーリー」を支えるツール特集

#Looker

紗紗

2020.12.14

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

この記事では、こちらの記事で紹介されている、前年同日比もしくは前月同日比分析といったPoP分析（Period over Period 分析）の8つの実装ユースケースのSQLを、SnowflakeのSQLに対応したものに書き換えて実装してみます。

第一回は、dimension groupのtimeframeを利用したシンプルな実装です。

Liquidを使わずにとてもシンプルに実装できる一方、ダッシュボードフィルターやフィルターメジャーが利用できないのが難点です。

LookML

使用データ

ECサイトデータを利用して、注文日の売上の各種PoP(前週同日比(WTD)、前月同日比(MTD)、前年同日比(YTD))を出します。

  dimension_group: created {
    label: "受注"
    type: time
    # timeframeを指定しないと全てのtimeframeが表示されます。
    sql: ${TABLE}.created_at ;;
  }
  
  measure: total_sale_price {
    label: "総売上"
    type: sum
    value_format_name: usd
    sql: ${sale_price} ;;
  }

dateやmonthだけではなく、timeframeのオプションにはdate_of_week(曜日)やdate_of_month(1~31)、date_of_year(1~335/336)という項目があるので、これらを利用します。

比較する日を抜き出す

考え方としては、WTD(前週同日比)、MTD(前月同日比)、YTD(前年同日比)を出すために、それぞれdimensionを定義して、sqlパラメーターでCURRENT_TIMESTAMPと上記のcreated_raw(受注日時生データ)を比較して評価するします。

  dimension: wtd_only {
    group_label: "To-Date Filters"
    label: "WTD"
    view_label: "_PoP"
    type: yesno
    sql: (EXTRACT(DOW FROM ${created_raw}) < EXTRACT(DOW FROM CURRENT_TIMESTAMP)
            OR
         (EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM CURRENT_TIMESTAMP)
              AND
          EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM CURRENT_TIMESTAMP))
            OR
         (EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM CURRENT_TIMESTAMP)
              AND
          EXTRACT(HOUR FROM ${created_raw}) = EXTRACT(HOUR FROM CURRENT_TIMESTAMP)
              AND
          EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM CURRENT_TIMESTAMP))
        )
    ;;
  }

  dimension: mtd_only {
    group_label: "To-Date Filters"
    label: "MTD"
    view_label: "_PoP"
    type: yesno
    sql: (EXTRACT(DAY FROM ${created_raw}) < EXTRACT(DAY FROM CURRENT_TIMESTAMP)
            OR
          (EXTRACT(DAY FROM ${created_raw}) = EXTRACT(DAY FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM CURRENT_TIMESTAMP))
            OR
          (EXTRACT(DAY FROM ${created_raw}) = EXTRACT(DAY FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(HOUR FROM ${created_raw}) = EXTRACT(HOUR FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM CURRENT_TIMESTAMP))
        )
    ;;
  }

  dimension: ytd_only {
    group_label: "To-Date Filters"
    label: "YTD"
    view_label: "_PoP"
    type: yesno
    sql: (EXTRACT(DOY FROM ${created_raw}) < EXTRACT(DOY FROM CURRENT_TIMESTAMP)
            OR
          (EXTRACT(DOY FROM ${created_raw}) = EXTRACT(DOY FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM CURRENT_TIMESTAMP))
            OR
          (EXTRACT(DOY FROM ${created_raw}) = EXTRACT(DOY FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(HOUR FROM ${created_raw}) = EXTRACT(HOUR FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM CURRENT_TIMESTAMP))
        )
    ;;
  }

Exploreで可視化する

PoPの種類を選択する
先ほどLookMLで定義したdimensionが下の画像のように出てきますので、比較したい粒度のdimensionをフィルターをかけます。
フィルターをかけるとyesが選択されて出てきます。
x軸になるtimeframeを選択する(Pivotしないdimension)

下のPivotする方と紛らわしいですが、軸になるのは[Day of xxx]です。なので1で選択した粒度に応じて、[Day of Week]か[Day of Month]、[Day of Year]を選択します。
Pivotするtimeframeを選択する

Pivotするtimeframeは[Week]、[Month]、[Year]です。